﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_ExportDetail.cs
// 创建时间：2016/11/27
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  入库当详情表.
    /// </summary>
    public partial class ERP_ExportDetailDAL : IERP_ExportDetailDAL
    {
        //此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_ExportDetail表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
        //注意：所写sql语句执行结果的字段必须是唯一的

        private const string QueryDataString = @" select c.*,
a.CargoId,a.SupplyId,a.Supplyprice,b.CargoName,
b.Spell,b.ByName,b.CargoCode,b.Spec,b.Version,b.ProduceName,b.Area,b.Unit,
d.StoreRoomId,
(select SupplyName from ERP_Supply where SupplyID=a.SupplyID )  SupplyName,
(select TYPE_NAME from ERP_Type where TYPE_ID=b.TypeId )  TypeName,
(select Use_NAME from ERP_Use where Use_ID=b.UseId )  UseNAME,
(a.Supplyprice * c.Count) TotalPrice
from ERP_ExportDetail c
inner join ERP_ExportBll d on c.DExportBillId = d.ExportBillId
inner join  ERP_Cargo_Detail a on a.DetailId=c.DetailId
inner join ERP_Cargo b on a.CargoId=b.CargoId  ";



        string QueryInStoreDatilSqlStr = @"
select a.*,
b.State,b.ExportBillNo,b.ReceiptNo,
b.CheckDate,
b.StoreRoomId,
(select STOREROOM_NAME from VIEW_STORE where STOREROOM_ID=b.StoreRoomId ) STOREROOM_NAME,
b.SupplyId,
(select SupplyName from ERP_Supply where SupplyID=b.SupplyId ) SupplyName,
c.Supplyprice,c.Supplyprice*a.count SumSupplyprice,
d.CargoName,d.CargoCode,d.Spec,d.Version,d.ProduceName,d.Area,d.Unit
from ERP_ExportDetail a
inner join ERP_ExportBll b on a.DExportBillId = b.ExportBillId
inner join ERP_Cargo_Detail c on a.DetailId = c.DetailId
inner join ERP_Cargo d on c.CargoId = d.CargoId ";

        public DataTable QueryInStoreDatil(int iPageSize, int iPageIndex, string sWhere, string sOder)
        {
            string tableStr = "(" + QueryInStoreDatilSqlStr + ") SQL_A";
            if (sOder.Length <= 0)
                sOder = "ItemlId";
            string sqlStr = "select * from (select ROW_NUMBER() OVER(order by " + sOder + " ) as row, * from " + tableStr;
            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;
            sqlStr += ") SQL1 where row between " + Convert.ToString(iPageSize * (iPageIndex - 1) + 1) + " and " + Convert.ToString(iPageSize * iPageIndex);
            WriteLog("ERP_ExportDetailDAL", "QueryInStoreDatil(int iPageSize,int iPageIndex, string sWhere, string sOder)", sqlStr);
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }

        public int QueryInStoreDatilRecorderCount( string sWhere)
        {
            string tableStr = "(" + QueryInStoreDatilSqlStr + ") SQL_A";
            string sqlStr = "select Count(*) from " + tableStr;

            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;

            WriteLog("ERP_ExportDetailDAL", "QueryInStoreDatilRecorderCount(string sWhere)", sqlStr);

            return Convert.ToInt32(SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.Text, sqlStr));
        }

    }
}

